using Oracle.DataAccess.Client;
using System.Data;
using System.Configuration;
using System;
using System.Globalization;

/// <summary>
/// Author: AustralTek 
/// Create date: 2010/05/04
/// http://www.australtek.com
/// Copyright: 2009 - 2010
/// </summary>

public partial class DALService : IDALService
{
    #region "Methods"
    public DataSet GetGenericSQLDataAdapterForOracle(ProcedureParameters cp)
    {
        try
        {
            OracleConnection oraCon = new OracleConnection();
            oraCon.ConnectionString = ConfigurationManager.ConnectionStrings[cp.DatabaseParameters.ConnectionStringName].ConnectionString;
            oraCon.Open();
            OracleCommand comm = new OracleCommand();
            comm.Connection = oraCon;
            comm.CommandText = cp.DatabaseParameters.Procedure;
            comm.CommandType = CommandType.StoredProcedure;

            /*
             * IMPORTANT!!!
             * To use an Oracle database engine it is necesary to send the parameters in the right order like they are in the procedure.
             * Not like with SQL Server engine for example.
             * Remember also set the right coutn of tables and the right order the names of the tables(optional) in the javascript.
            */
            if (cp.DatabaseParameters.QueryParameters != null)
            {
                foreach (QueryParameter param in cp.DatabaseParameters.QueryParameters)
                {
                    OracleParameter oracParam = new OracleParameter(param.Name, (OracleDbType)param.Type);
                    if ((OracleDbType)param.Type == OracleDbType.Date)
                    {
                        oracParam.Value = DateTime.Parse((string)param.Value, CultureInfo.GetCultureInfo("en-US"));
                    }
                    else
                    {
                        oracParam.Value = param.Value;
                    }
                    comm.Parameters.Add(oracParam);
                }
            }

            if (cp.DatabaseParameters.TablesCount == 0)
                throw new UserException("Problems executing procedure" + cp.DatabaseParameters.Procedure + ".\r\n  Error: Tables count can not be zero.");
            else
                for (int iTable = 0; iTable < cp.DatabaseParameters.TablesCount; iTable++)
                    comm.Parameters.Add(new OracleParameter("t" + iTable + "_cursor", OracleDbType.RefCursor, ParameterDirection.Output));

            DataSet ds = new DataSet();
            OracleDataAdapter da = new OracleDataAdapter(comm);
            da.Fill(ds);
            da.Dispose();
            oraCon.Close();
            return ds;

        }
        catch (OracleException oex)
        {
            if (oex.Message.Contains("wrong number or types of arguments"))
                throw new UserException("Problems executing procedure" + cp.DatabaseParameters.Procedure + ".\r\n  Error: Wrong number,order or types of parameters or the tables count or the tables names.");
            else
                throw new UserException("Problems executing procedure" + cp.DatabaseParameters.Procedure + ".\r\n  Error: " + oex.Message);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    #endregion
}
